package com.fau.hm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;
import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Hibernate;
import org.springframework.context.support.MessageSourceAccessor;
import org.springframework.security.authentication.encoding.Md5PasswordEncoder;
import org.springframework.security.core.SpringSecurityMessageSource;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.hm.entity.MedicalAdmin;
import com.fau.hm.entity.MedicalArchivalInfo;
import com.fau.hm.entity.MedicalGroup;
import com.fau.hm.entity.MedicalKeyValue;
import com.fau.hm.entity.MedicalResource;
import com.fau.hm.entity.MedicalRole;
import com.fau.hm.entity.MedicalStaffSalary;
import com.fau.hm.service.IUserManagerService;
import com.fau.hm.util.ExcelUtil;
import com.fau.hm.util.Page;
import com.fau.hm.util.ParamUtil;
import com.fau.hm.util.SystemConfig;
import com.fau.hm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class UserManagerService extends BaseService implements IUserManagerService {
	
	protected final MessageSourceAccessor messages = SpringSecurityMessageSource.getAccessor();
	private Md5PasswordEncoder encoder;

	@SuppressWarnings("deprecation")
	@Override
	public MedicalAdmin findUserByName(String username) {
		MedicalAdmin admin = (MedicalAdmin) iBaseDao.getObjectByHql("from MedicalAdmin a where a.username = ?", username);
		if (ObjectUtils.equals(null, admin)) 
			throw new UsernameNotFoundException(messages.getMessage("JdbcDaoImpl.notFound", new Object[]{username}, "Username {0} not found"), username);
			Hibernate.initialize(admin.getRoleSet());
		if(0 == admin.getRoleSet().size())
			throw new UsernameNotFoundException(messages.getMessage("JdbcDaoImpl.noAuthority", new Object[] {username}, "User {0} has no GrantedAuthority"), username);
		return admin;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalResource> findAllResource() {
		List<MedicalResource> list = iBaseDao.getBySql("from MedicalResource a where a.typeName = ?", null, SystemConfig.HM_SYSTEM_TYPE);
		for(MedicalResource mr : list) {
			Hibernate.initialize(mr.getRoleSet());
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalResource> findRootResource() {
		List<MedicalResource> list = this.iBaseDao.getBySql("from MedicalResource a where a.isMenus=? ", null, SystemConfig.ROOT_RESOURCE_TYPE);
		for(MedicalResource mr : list) {
			Hibernate.initialize(mr.getRoleSet());
		}
		return list;
	}
	
	public List<MedicalResource> loadMenu() {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		List<MedicalResource> list = new ArrayList<MedicalResource>();
		for(MedicalRole mr : user.getRoleSet()) {
			MedicalRole role = (MedicalRole) iBaseDao.getObject(MedicalRole.class, mr.getId());
			for(MedicalResource resource : role.getResourceSet()) {
				if(SystemConfig.IS_MENUS == resource.getIsMenus() && SystemConfig.HM_SYSTEM_TYPE.equals(resource.getTypeName())){
					list.add(resource);
				}
			}
		}
		for(int i = 0; i < list.size(); i++) {
			for(int j = list.size() - 1; j > i; j--) {
				if(list.get(i).getId().equals(list.get(j).getId())){
					list.remove(j);
				}
			}
		}
		return list;
	}
	
	public Md5PasswordEncoder getEncoder() {
		return encoder;
	}

	@Resource
	public void setEncoder(Md5PasswordEncoder encoder) {
		this.encoder = encoder;
	}

	@Override
	public Page findAllUsers(Map<String, Object> map, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalAdmin a where 1=1 and a.isSuper = '0' ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalAdmin a where 1=1 and a.isSuper = '0' ");
		
		List<Object> objectList = new ArrayList<Object>();
		
		if(StringUtils.isNotBlank(admin.getUsername())){
			statusSql.append(" and a.username = ? ");
			countSql.append(" and a.username = ? ");
			objectList.add(admin.getUsername());
		}
		
		if(StringUtils.isNotBlank(admin.getName())){
			String str = admin.getName().trim().replaceAll("_", "chr(95)").replaceAll("%", "chr(37)");
			statusSql.append(" and a.name like ? ");
			countSql.append(" and a.name like ? ");
			objectList.add("%"+str+"%");
		}
		
		if(StringUtils.isNotBlank(admin.getJobStatus())){
			String[] str = admin.getJobStatus().split(",");
			statusSql.append(" and (a.jobStatus = ? ");
			countSql.append(" and (a.jobStatus = ? ");
			objectList.add(str[0]);
			if(str.length > 1){
				statusSql.append(" or a.jobStatus = ? ");
				countSql.append(" or a.jobStatus = ? ");
				objectList.add(str[1]);
			}
			statusSql.append(")");
			countSql.append(")");
		}
		
		if(StringUtils.isNotBlank(admin.getDeptType())){
			String[] str = admin.getDeptType().split(",");
			statusSql.append(" and (a.deptType = ? ");
			countSql.append(" and (a.deptType = ? ");
			objectList.add(str[0]);
			if(str.length > 1){
				for(int i=1;i<str.length;i++){
					statusSql.append(" or a.deptType = ? ");
					countSql.append(" or a.deptType = ? ");
					objectList.add(str[i]);
				}
			}
			statusSql.append(")");
			countSql.append(")");
		}
		
		if(StringUtils.isNotBlank(admin.getGroupId())){
			String[] str = admin.getGroupId().split(",");
			statusSql.append(" and (a.group.id = ? ");
			countSql.append(" and (a.group.id = ? ");
			objectList.add(str[0]);
			if(str.length > 1){
				for(int i=1;i<str.length;i++){
					statusSql.append(" or a.group.id = ? ");
					countSql.append(" or a.group.id = ? ");
					objectList.add(str[i]);
				}
			}
			statusSql.append(")");
			countSql.append(")");
		}

		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0] );
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalAdmin> userList = new ArrayList<MedicalAdmin>();
		for(Object obj : page.getResult()){
			MedicalAdmin user = (MedicalAdmin) obj;
			MedicalKeyValue dept = this.iBaseDao.getValueByKey("0", SystemConfig.HM_SYSTEM_TYPE, user.getDeptType());
			user.setDeptName(dept.getKeyValue());
			MedicalKeyValue keyValue = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), SystemConfig.HM_SYSTEM_TYPE, user.getJobStatus());
			user.setStatus(keyValue.getKeyValue());
			if(SystemConfig.CM_SYSTEM_TYPE.equals(user.getDeptType()) || SystemConfig.OM_SYSTEM_TYPE.equals(user.getDeptType())) {
				Hibernate.initialize(user.getGroup());
			}
			Hibernate.initialize(user.getRoleSet());
			if(null != user.getRoleSet()) {
				StringBuilder roleId = new StringBuilder();
				for(Iterator<MedicalRole> iterator = user.getRoleSet().iterator();iterator.hasNext();){  
					roleId.append(iterator.next().getId()+",");
				}
				if(roleId.length() > 0) {
					roleId.deleteCharAt(roleId.lastIndexOf(","));
				}
				user.setRoleId(roleId.toString());
			}
			userList.add(user);
		}
		page.setResult(userList);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalGroup> findGroupByEid(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalGroup m where m.eid = ? ", null, admin.getEnterpriseId());
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean save(MedicalAdmin admin) {
		MedicalGroup group = new MedicalGroup();
		String groupId = "2";	// 超级管理员为 1 , 非话务、订单中心为 2
		if(StringUtils.isNotBlank(admin.getGroupId())) {
			groupId = admin.getGroupId();
		}
		group.setId(groupId);
		admin.setGroup(group);
		if(StringUtils.isBlank(admin.getIsLeader())) {
			admin.setIsLeader("0");
		}
		Set<MedicalRole> roleSet = new HashSet<MedicalRole>();
		String[] roleIds = admin.getRoleId().split(",");
		for(String rid : roleIds) {
			MedicalRole role = new MedicalRole();
			role.setId(rid);
			roleSet.add(role);
		}
		admin.setRoleSet(roleSet);
		admin.setIsSuper("0");
		admin.setPassword(encoder.encodePassword(admin.getPassword(), admin.getUsername()));
		admin.setJobStatus(SystemConfig.JOB_WORK_STATUS);
		admin.setOnlineStatus(SystemConfig.OFFLINE_STATUS);
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(admin);
		if(errorInfo.size() > 0) {
			return false;
		} else {
			boolean flag = this.iBaseDao.saveObject(admin);
			//同时保存一条记录到档案表
			MedicalArchivalInfo info = new MedicalArchivalInfo();
			info.setUser(admin);
			Set<ConstraintViolation<Object>> eInfo = ValidatorUtil.validateObj(info);
			if(eInfo.size() > 0) {
				return false;
			} else {
				this.iBaseDao.saveObject(info);
			}
			
			return flag;
		}
	}

	@Override
	public MedicalAdmin findById(String id) {
		return (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, id);
	}

	@Override
	public boolean findUserByUserName(String eid, String username) {
		int count = this.iBaseDao.getIntCount("select count(*) from MedicalAdmin u where u.enterpriseId = ? and u.username = ? ", eid, username);
		if(count > 0){
			return true;
		}else{
			return false;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalRole> findRoleByEid(MedicalAdmin admin) {
		List<MedicalRole> list = this.iBaseDao.getBySql("from MedicalRole r where r.eid = ? and r.id <> ? ", null, admin.getEnterpriseId(), SystemConfig.SUPER_ID);
		return list;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveEdit(MedicalAdmin admin) {
		MedicalAdmin user = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, admin.getId());
		user.setName(admin.getName());
		user.setDeptType(admin.getDeptType());
		if(StringUtils.isNotBlank(admin.getPassword())){
			user.setPassword(encoder.encodePassword(admin.getPassword(), user.getUsername()));
		}
		user.setDescription(admin.getDescription());
		user.setJobStatus(admin.getJobStatus());
		MedicalGroup group = new MedicalGroup();
		String groupId = "2";	// 超级管理员为 1 , 非话务、订单系统 2
		if(StringUtils.isNotBlank(admin.getGroupId())) {
			groupId = admin.getGroupId();
		}
		group.setId(groupId);
		user.setGroup(group);
		if(StringUtils.isBlank(admin.getIsLeader())) {
			admin.setIsLeader("0");
		}
		user.setIsLeader(admin.getIsLeader());
		user.setMobile(admin.getMobile());
		user.setPhoneNumber(admin.getPhoneNumber());
		Set<MedicalRole> roleSet = new HashSet<MedicalRole>();
		user.getRoleSet().clear();
		String[] roleIds = admin.getRoleId().split(",");
		for(String rid : roleIds) {
			MedicalRole role = new MedicalRole();
			role.setId(rid);
			roleSet.add(role);
		}
		user.setRoleSet(roleSet);
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(user);
		if(errorInfo.size() > 0) {
			return false;
		} else {
			return this.iBaseDao.saveObject(user);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delUser(MedicalAdmin admin) {
		String ids[] = admin.getId().split(",");
		if(ids.length == 1){
			//删除档案表信息
			this.iBaseDao.updateHql("delete MedicalArchivalInfo m where m.user.id = ? ", admin.getId());
			
			//删除绩效考核
			this.iBaseDao.updateHql("delete MedicalCallCheck m where m.user.id = ? ", admin.getId());
			
			//删除员工绩效薪资数据
			@SuppressWarnings("unchecked")
			List<MedicalStaffSalary> list1 = this.iBaseDao.getBySql("from MedicalStaffSalary m where m.staffBasic.user.id = ? ",null, admin.getId());
			if(list1.size() > 0){
				this.iBaseDao.updateHql("delete MedicalStaffBasic m where m.user.id = ? ", admin.getId());
				this.iBaseDao.updateHql("delete MedicalStaffSalary m where m.staffBasic.user.id = ? ", admin.getId());
			}
			
			//删除员工的请假记录
			this.iBaseDao.updateHql("delete MedicalLeaveInfo m where m.user.id = ? ", admin.getId());
			this.iBaseDao.updateHql("delete MedicalLeaveApprove m where m.user.id = ? ", admin.getId());
			this.iBaseDao.updateHql("delete MedicalLeaveNotBy m where m.user.id = ? ", admin.getId());
			
			this.iBaseDao.deleteObject(admin);
		}else{
			for(String id : ids){
				//删除档案表信息
				this.iBaseDao.updateHql("delete MedicalArchivalInfo m where m.user.id = ? ", id);
				
				//删除绩效考核
				this.iBaseDao.updateHql("delete MedicalCallCheck m where m.user.id = ? ", id);
				
				//删除员工绩效薪资数据
				@SuppressWarnings("unchecked")
				List<MedicalStaffSalary> list1 = this.iBaseDao.getBySql("from MedicalStaffSalary m where m.staffBasic.user.id = ? ",null, admin.getId());
				if(list1.size() > 0){
					this.iBaseDao.updateHql("delete MedicalStaffBasic m where m.user.id = ? ", id);
					this.iBaseDao.updateHql("delete MedicalStaffSalary m where m.staffBasic.user.id = ? ", id);
				}
				
				//删除员工的请假记录
				this.iBaseDao.updateHql("delete MedicalLeaveInfo m where m.user.id = ? ", id);
				this.iBaseDao.updateHql("delete MedicalLeaveApprove m where m.user.id = ? ", id);
				this.iBaseDao.updateHql("delete MedicalLeaveNotBy m where m.user.id = ? ", id);
				
				this.iBaseDao.updateHql("delete MedicalAdmin m where m.id = ? ", id);
			}
		}
	}

	@Override
	public Workbook currentPage(String ids) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EMPLOYEE_INFO_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(ids)){
			String [] uIds =  ids.split(",");
			for(int j=0;j<uIds.length;j++){
				// 获得记录
				MedicalAdmin user = (MedicalAdmin)this.iBaseDao.getObject(MedicalAdmin.class, uIds[j]);
				if(null != user){
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(user.getName());
					currentRow.createCell(2).setCellValue(user.getUsername());
					MedicalKeyValue dept = this.iBaseDao.getValueByKey("0", SystemConfig.HM_SYSTEM_TYPE, user.getDeptType());
					currentRow.createCell(3).setCellValue(dept.getKeyValue());
					if(user.getDeptType().equals(SystemConfig.CM_SYSTEM_TYPE) || user.getDeptType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
						Hibernate.initialize(user.getGroup());
						if(null != user.getGroup()) {
							currentRow.createCell(4).setCellValue(user.getGroup().getName());
							currentRow.createCell(6).setCellValue(user.getGroup().getPlatform().getName());
						}
					}
					MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(user.getEnterpriseId(), SystemConfig.HM_SYSTEM_TYPE, user.getJobStatus());
					currentRow.createCell(5).setCellValue(keyVal.getKeyValue());
					if(null != user.getCreateTime()){
						currentRow.createCell(7).setCellValue(DateFormatUtils.format(user.getCreateTime(), "yyyy-MM-dd HH:mm"));
					}
					if(null != user.getLastLoginTime()){
						currentRow.createCell(8).setCellValue(DateFormatUtils.format(user.getLastLoginTime(), "yyyy-MM-dd HH:mm"));
					}
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("all")
	@Override
	public Workbook allPage(MedicalAdmin admin) throws Exception {
		List<MedicalAdmin> list = this.iBaseDao.getBySql("from MedicalAdmin u where 1=1 and u.enterpriseId = ? order by u.createTime desc", null, admin.getEnterpriseId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EMPLOYEE_INFO_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalAdmin user : list){
			if(SystemConfig.SUPER_ADMIN.equals(user.getIsSuper())) continue;
			i++;
			if(null != user){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(user.getName());
				currentRow.createCell(2).setCellValue(user.getUsername());
				MedicalKeyValue dept = this.iBaseDao.getValueByKey("0", SystemConfig.HM_SYSTEM_TYPE, user.getDeptType());
				currentRow.createCell(3).setCellValue(dept.getKeyValue());
				if(user.getDeptType().equals(SystemConfig.CM_SYSTEM_TYPE) || user.getDeptType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
					Hibernate.initialize(user.getGroup());
					if(null != user.getGroup()) {
						currentRow.createCell(4).setCellValue(user.getGroup().getName());
						currentRow.createCell(6).setCellValue(user.getGroup().getPlatform().getName());
					}
				}
				MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(user.getEnterpriseId(), SystemConfig.HM_SYSTEM_TYPE, user.getJobStatus());
				currentRow.createCell(5).setCellValue(keyVal.getKeyValue());
				if(null != user.getCreateTime()){
					currentRow.createCell(7).setCellValue(DateFormatUtils.format(user.getCreateTime(), "yyyy-MM-dd HH:mm"));
				}
				if(null != user.getLastLoginTime()){
					currentRow.createCell(8).setCellValue(DateFormatUtils.format(user.getLastLoginTime(), "yyyy-MM-dd HH:mm"));
				}
			}
		}
		return workbook;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void importSave(MedicalAdmin admin, File file) throws Exception {
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		// 循环导入
		while(rows.hasNext()){
			row = rows.next();
			if(row.getRowNum() == 0) continue;
			MedicalAdmin user = new MedicalAdmin();
			if(null == row.getCell(0) || "".equals(row.getCell(0))) continue;
			if(null != getValue(row.getCell(0))){
				user.setName(getValue(row.getCell(0)));
			}
			if(null != getValue(row.getCell(1))){
				boolean flag = this.findUserByUserName(admin.getEnterpriseId(), getValue(row.getCell(1)));
				if(flag) continue;
				user.setUsername(getValue(row.getCell(1)));
			}
			user.setPassword(encoder.encodePassword(getValue(row.getCell(2)), user.getUsername()));
			MedicalKeyValue dept = (MedicalKeyValue) this.iBaseDao.getObjectByHql("from MedicalKeyValue a where a.hospitalId=? and a.systemType=? and a.keyValue = ?",
					"0", SystemConfig.HM_SYSTEM_TYPE, getValue(row.getCell(3)));
			if(null == dept) continue;
			user.setDeptType(dept.getKeyName());
			MedicalGroup group = this.findByGroupNameAndEid(admin.getEnterpriseId(), getValue(row.getCell(4)));
			if(null == group) {
				group = new MedicalGroup();
				group.setId("1");
			}
			user.setGroup(group);
			MedicalRole role = this.findByRoleNameAndEid(admin.getEnterpriseId(), getValue(row.getCell(5)));
			if(null == role) continue;
			Set<MedicalRole> roleSet = new HashSet<MedicalRole>();
			roleSet.add(role);
			user.setRoleSet(roleSet);
			if(null != row.getCell(6)){
				user.setMobile(getValue(row.getCell(6)));
			}
			if(null != row.getCell(7)){
				user.setDescription(getValue(row.getCell(7)));
			}
			user.setEnterpriseId(admin.getEnterpriseId());
			user.setIsSuper("0");
			user.setCreateIp(admin.getCreateIp());
			user.setJobStatus(SystemConfig.JOB_WORK_STATUS);
			Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(admin);
			if(errorInfo.size() > 0) {
				continue;
			} else {
				this.iBaseDao.saveObject(user);
				//同时保存一条记录到档案表
				MedicalArchivalInfo info = new MedicalArchivalInfo();
				info.setUser(user);
				this.iBaseDao.saveObject(info);
			}
		}
	}
  
	@Override
	public Workbook template(MedicalAdmin admin) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EMPLOYEE_INFO_TEMPLATE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		List<MedicalKeyValue> deptList = this.iBaseDao.findValueByType("0", SystemConfig.HM_SYSTEM_TYPE, SystemConfig.DEPT_TYPE);
		List<String> deptNameList = new ArrayList<String>();
		for(MedicalKeyValue dept : deptList){
			deptNameList.add(dept.getKeyValue());
		}
		String[] deptlist = deptNameList.toArray(new String[deptNameList.size()]);
		List<MedicalGroup> groupList = this.findGroupByEid(admin);
		List<String> groupNameList = new ArrayList<String>();
		for(MedicalGroup group : groupList){
			groupNameList.add(group.getName());
		}
		String[] grouplist = groupNameList.toArray(new String[groupNameList.size()]);
		List<MedicalRole> roleList = this.findRoleByEid(admin);
		List<String> roleNameList = new ArrayList<String>();
		for(MedicalRole role : roleList){
			roleNameList.add(role.getName());
		}
		String[] rolelist = roleNameList.toArray(new String[roleNameList.size()]);
		sheet = ExcelUtil.setPullDown(sheet, deptlist, 1, 500, 3, 3);	// 第一列的前501行都设置为选择列表形式.
		sheet = ExcelUtil.setPullDown(sheet, grouplist, 1, 500, 4, 4);	// 第一列的前501行都设置为选择列表形式.
		sheet = ExcelUtil.setPullDown(sheet, rolelist, 1, 500, 5, 5);	// 第一列的前501行都设置为选择列表形式.	
		return workbook;
	}

	private MedicalGroup findByGroupNameAndEid(String eid, String name) {
		return (MedicalGroup) this.iBaseDao.getObjectByHql("from MedicalGroup m where m.eid = ? and m.name = ? ", eid, name);
	}

	private MedicalRole findByRoleNameAndEid(String eid, String name) {
		return (MedicalRole) this.iBaseDao.getObjectByHql("from MedicalRole m where m.eid = ? and m.name = ? ", eid, name);
	}
	
	/**
	 * 列值类型的转换
	 * @author dangjh
	 * @param cell
	 * @return
	 * 2014年3月13日 下午5:56:51
	 */
	private static String getValue(Cell cell) {  
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(cell.getBooleanCellValue());  
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        	String cellValue = String.valueOf(cell.getNumericCellValue());
        	if(cellValue.contains(".")){
        		cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
        	}
            return cellValue;  
        } else {
        	cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());  
        }  
    }

}
